package com.ai.view;

import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.ai.mmd.reviewed.vo.SbmxinfoExtVO;

/**
 * 导出特色航线Excel视图
 * 
 * @author Andy
 *
 */
public class ZdViewExcel extends AbstractExcelView {

	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		String excelName = "站点出入记录.xls";
		// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8"));

		//标题样式 start
		HSSFCellStyle headStyle = workbook.createCellStyle();
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  

		HSSFFont titlefont = workbook.createFont();
		titlefont.setFontHeightInPoints((short) 22);
		headStyle.setFont(titlefont);
		//标题样式 end
		
		//单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		
		// 产生Excel表头
		HSSFSheet sheetwjs = workbook.createSheet("吴家山");
		HSSFSheet sheetsk = workbook.createSheet("滠口");

		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 11);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		titleStyle.setFont(font);
		
		//添加标题
		HSSFRow titlewjs = sheetwjs.createRow(0);
		titlewjs.setHeight((short) 500);
		HSSFCell hsstitlewjs = titlewjs.createCell(0);
		hsstitlewjs.setCellValue("站点出入记录");
		hsstitlewjs.setCellStyle(headStyle);
		sheetwjs.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
		
		HSSFRow titlesk = sheetsk.createRow(0);
		titlesk.setHeight((short) 500);
		HSSFCell hsstitlesk = titlesk.createCell(0);
		hsstitlesk.setCellValue("站点出入记录");
		hsstitlesk.setCellStyle(headStyle);
		sheetsk.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

		HSSFRow headwjs = sheetwjs.createRow(1);
		HSSFRow headsk = sheetsk.createRow(1);
		headwjs.setHeight((short) 400);
		headsk.setHeight((short) 400);
		
		for(int i=1;i<4;i++){
			sheetwjs.setColumnWidth(i, 5000);
			sheetsk.setColumnWidth(i, 5000);
		}	
		
		HSSFCell h_wjsxl = headwjs.createCell(0);
		HSSFCell h_skxl = headsk.createCell(0);
		h_wjsxl.setCellValue("序号");
		h_skxl.setCellValue("序号");
		h_wjsxl.setCellStyle(titleStyle);
		h_skxl.setCellStyle(titleStyle);
		
		HSSFCell h_wjsxh = headwjs.createCell(1);
		HSSFCell h_skxh = headsk.createCell(1);
		h_wjsxh.setCellValue("箱号");
		h_skxh.setCellValue("箱号");
		h_wjsxh.setCellStyle(titleStyle);
		h_skxh.setCellStyle(titleStyle);

		HSSFCell h_wjscrzdsj = headwjs.createCell(2);
		HSSFCell h_skcrzdsj = headsk.createCell(2);
		h_wjscrzdsj.setCellValue("出/入铁路站场时间");
		h_skcrzdsj.setCellValue("出/入铁路站场时间");
		h_wjscrzdsj.setCellStyle(titleStyle);
		h_skcrzdsj.setCellStyle(titleStyle);
		
		HSSFCell h_wjstlhph = headwjs.createCell(3);
		HSSFCell h_sktlhph = headsk.createCell(3);
		h_wjstlhph.setCellValue("铁路货票号");
		h_sktlhph.setCellValue("铁路货票号");
		h_wjstlhph.setCellStyle(titleStyle);
		h_sktlhph.setCellStyle(titleStyle);

		List<SbmxinfoExtVO> zdwjs = (List<SbmxinfoExtVO>) model.get("zdwjs");
		List<SbmxinfoExtVO> zdsk = (List<SbmxinfoExtVO>) model.get("zdsk");
		
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		
		for (int i=2;i<=zdwjs.size()+1;i++) {
			HSSFRow row = sheetwjs.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//箱号
			HSSFCell r_xh = row.createCell(1);
			HSSFRichTextString richxh = new HSSFRichTextString(zdwjs.get(i-2).getXh());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//出/入铁路站场时间
			HSSFCell r_crtlsj = row.createCell(2);
			HSSFRichTextString richcrtlsj = new HSSFRichTextString(format.format(zdwjs.get(i-2).getCrtlsj()));
			r_crtlsj.setCellValue(richcrtlsj);
			r_crtlsj.setCellStyle(cellStyle);
			
			//铁路货票号
			HSSFCell r_tlhph = row.createCell(3);
			HSSFRichTextString richtlhph = new HSSFRichTextString(zdwjs.get(i-2).getTlhph());
			r_tlhph.setCellValue(richtlhph);
			r_tlhph.setCellStyle(cellStyle);
		}

		for (int i=2;i<=zdsk.size()+1;i++) {
			HSSFRow row = sheetsk.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//箱号
			HSSFCell r_xh = row.createCell(1);
			HSSFRichTextString richxh = new HSSFRichTextString(zdsk.get(i-2).getXh());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//出/入铁路站场时间
			HSSFCell r_crtlsj = row.createCell(2);
			HSSFRichTextString richcrtlsj = new HSSFRichTextString(format.format(zdsk.get(i-2).getCrtlsj()));
			r_crtlsj.setCellValue(richcrtlsj);
			r_crtlsj.setCellStyle(cellStyle);
			
			//铁路货票号
			HSSFCell r_tlhph = row.createCell(3);
			HSSFRichTextString richtlhph = new HSSFRichTextString(zdsk.get(i-2).getTlhph());
			r_tlhph.setCellValue(richtlhph);
			r_tlhph.setCellStyle(cellStyle);
		}
	}
}
